package cn.com.lms.business.exam.persistence.repository;

import cn.com.lms.business.exam.persistence.entity.EmployeePaperStage;
import com.bnzj.core.persistence.repository.BaseRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface EmployeePaperStageRepository extends BaseRepository<EmployeePaperStage, Long> {

    @Query(value = "select ps.stage_detail_id as stageDetailId, ps.employee_paper_id employeePaperId,p.paper_name as paperName,p.single_score as singleScore, p.multiple_score as multipleScore, p.judge_score as judgeScore, p.type as  paperType,ls.stage_name as stageName,pe.paper_score paperScore," +
            " sd.adopt_score adoptScore,sd.study_time as paperTime, ep.paper_id as paperId," +
            " ep.type,ep.employee_id as employeeId,ep.remain_time as remainTime,ep.limit_num as limitNum, ep.modify_time as modifyTime, ep.grade,ep.is_hand as isHand," +
            " (select count(1) from bnz_exam_employee_paper ep2 LEFT  JOIN  bnz_exam_employee_paper_stage ps2 on ep2.id =ps2.employee_paper_id where   ep2.employee_id=ps.employee_id and is_hand=1 and ps.paper_id=ep2.paper_id and ps2.stage_detail_id=ps.stage_detail_id GROUP BY ps2.stage_detail_id) as  handNum " +
            " from bnz_exam_employee_paper_stage ps left join bnz_exam_employee_paper ep on ep.id=ps.employee_paper_id " +
            " left join bnz_learning_stage_detail sd on ps.stage_detail_id=sd.id" +
            " left join bnz_learning_stage ls on ls.id=sd.stage_id" +
            " left join bnz_exam_paper_extra pe on pe.paper_id=ps.paper_id " +
            " left join bnz_exam_paper p on p.id=ps.paper_id " +
            "  where   ps.stage_detail_id=?1 and ps.employee_id=?2 order by ep.create_time desc", nativeQuery = true)
    List<Map> findEmployeePaperByStageDetailIdAndEmployeeId(Long stageDetailId, Long employeeId);

    @Query(value = "select ps.stage_detail_id as stageDetailId, ps.employee_paper_id employeePaperId,p.paper_name as paperName,p.single_score as singleScore, p.multiple_score as multipleScore, p.judge_score as judgeScore, p.type as  paperType,ls.stage_name as stageName,pe.paper_score paperScore," +
            " sd.adopt_score adoptScore,sd.study_time as paperTime, ep.paper_id as paperId," +
            " ep.type,ep.employee_id as employeeId,ep.remain_time as remainTime,ep.limit_num as limitNum, ep.modify_time as modifyTime, ep.grade,ep.is_hand as isHand," +
            " (select count(1) from bnz_exam_employee_paper ep2 LEFT  JOIN  bnz_exam_employee_paper_stage ps2 on ep2.id =ps2.employee_paper_id where   ep2.employee_id=ps.employee_id and is_hand=1 and ps.paper_id=ep2.paper_id and ps2.stage_detail_id=ps.stage_detail_id GROUP BY ps2.stage_detail_id) as  handNum " +
            " from bnz_exam_employee_paper_stage ps left join bnz_exam_employee_paper ep on ep.id=ps.employee_paper_id " +
            " left join bnz_learning_stage_detail sd on ps.stage_detail_id=sd.id" +
            " left join bnz_learning_stage ls on ls.id=sd.stage_id" +
            " left join bnz_exam_paper_extra pe on pe.paper_id=ps.paper_id " +
            " left join bnz_exam_paper p on p.id=ps.paper_id " +
            "  where   ps.stage_detail_id=?1 and ps.employee_id=?2 and ep.is_hand=1 and ep.grade>=sd.adopt_score", nativeQuery = true)
    Map findPassEmployeePaperByStageDetailIdAndEmployeeId(Long stageDetailId, Long employeeId);

    @Query(value = "select ps.stage_detail_id as stageDetailId, ps.employee_paper_id employeePaperId,p.paper_name as paperName,p.single_score as singleScore, p.multiple_score as multipleScore, p.judge_score as judgeScore, p.type as  paperType,ls.stage_name as stageName,pe.paper_score paperScore," +
            " sd.adopt_score adoptScore,sd.study_time as paperTime, ep.paper_id as paperId," +
            " ep.type,ep.employee_id as employeeId,ep.remain_time as remainTime,ep.limit_num as limitNum, ep.modify_time as modifyTime, ep.grade,ep.is_hand as isHand," +
            " (select count(1) from bnz_exam_employee_paper ep2 LEFT  JOIN  bnz_exam_employee_paper_stage ps2 on ep2.id =ps2.employee_paper_id where   ep2.employee_id=ps.employee_id and is_hand=1 and ps.paper_id=ep2.paper_id and ps2.stage_detail_id=ps.stage_detail_id GROUP BY ps2.stage_detail_id) as  handNum " +
            " from bnz_exam_employee_paper_stage ps left join bnz_exam_employee_paper ep on ep.id=ps.employee_paper_id " +
            " left join bnz_learning_stage_detail sd on ps.stage_detail_id=sd.id" +
            " left join bnz_learning_stage ls on ls.id=sd.stage_id" +
            " left join bnz_exam_paper_extra pe on pe.paper_id=ps.paper_id " +
            " left join bnz_exam_paper p on p.id=ps.paper_id " +
            "  where   ps.stage_detail_id=?1 and ps.employee_id=?2 and ep.is_hand=1 and ep.grade<sd.adopt_score order by ep.create_time desc  ", nativeQuery = true)
    Map findNotPassEmployeePaperByStageDetailIdAndEmployeeId(Long stageDetailId, Long employeeId);


    @Query(value = "select ps.stage_detail_id as stageDetailId, ps.employee_paper_id employeePaperId,p.paper_name as paperName,p.single_score as singleScore, p.multiple_score as multipleScore, p.judge_score as judgeScore, p.type as  paperType,ls.stage_name as stageName,pe.paper_score paperScore," +
            " sd.adopt_score adoptScore,sd.plan_id planId,sd.study_time as paperTime, ep.paper_id as paperId," +
            " ep.type,ep.employee_id as employeeId,ep.remain_time as remainTime,ep.modify_time as modifyTime,ep.grade,ep.is_hand as isHand" +
            " from bnz_exam_employee_paper_stage ps left join bnz_exam_employee_paper ep on ep.id=ps.employee_paper_id " +
            " left join bnz_learning_stage_detail sd on ps.stage_detail_id=sd.id" +
            " left join bnz_learning_stage ls on ls.id=sd.stage_id" +
            " left join bnz_exam_paper_extra pe on pe.paper_id=ps.paper_id " +
            " left join bnz_exam_paper p on p.id=ps.paper_id " +
            "  where ps.employee_paper_id=?1 ", nativeQuery = true)
    Map findEmployeePaperByEmployeePaperId(Long employeePaperId);
}
